package excel;

import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.usermodel.XSSFColor;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import java.io.File;
import java.io.FileInputStream;
import java.io.InputStream;
import java.time.LocalDate;
import java.time.format.DateTimeFormatter;
import java.time.temporal.ChronoUnit;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.stream.Collectors;
import java.util.stream.IntStream;

public class ExcelDemo {

    /**
     * java基础|file类的创建、相对路径的三种写法【建议收藏】
     * https://blog.csdn.net/mengxiang_/article/details/126355591
     * idea中的java文件比较特殊，是以src这个文件夹为主位置，而不是编写的java文件所存储的位置
     */
    public static void main(String[] args) throws Exception {
        // 使用这个看看获取到的目录是什么
        File file = new File(".");
        if (file.exists()) {
            System.out.println("根目录：" + file.getAbsolutePath());
        }
        // 不是相对自己
        // file = new File("../../../resources/template/年月日表头生成模板.xlsx");
        file = new File("java-tools/src/main/resources/template/年月日表头生成模板.xlsx");
        if (file.exists()) {
            System.out.println("文件路径：" + file.getAbsolutePath());
        } else {
            System.out.println("文件不存在");
        }

        // generateHeadExcel();

        redaExcel();
    }

    /**
     * 生成年月日表头
     * 用于制作项目进度甘特图 条件格式
     */
    private static void generateHeadExcel() throws Exception {
        // 年份，日期
        Map<Integer, List<LocalDate>> map = new HashMap<>();
        DateTimeFormatter dateTimeFormatter = DateTimeFormatter.ofPattern("yyyy-MM-dd");
        DateTimeFormatter dateTimeFormatter2 = DateTimeFormatter.ofPattern("yyyy/MM/dd");
        for (int i = 2022; i <= 2024; i++) {
            System.out.println(i);
            String start = i + "-01-01";
            String end = i + "-12-31";
            LocalDate startDate = LocalDate.parse(start);
            LocalDate endDate = LocalDate.parse(end);

            List<LocalDate> dateList = getDatesBetween(startDate, endDate);
            dateList.forEach(o -> System.out.println(o));

            System.out.println(endDate.format(dateTimeFormatter)); // 格式化为字符串
            System.out.println(endDate.getYear());
            System.out.println(endDate.getMonth()); // 英文
            System.out.println(endDate.getMonthValue()); // 数字

            map.put(i, dateList);
        }
        System.out.println(map);

        // springboot获取模板文件
        // ClassPathResource classPathResource = new ClassPathResource("static/template/年月日表头生成模板.xlsx");
        // InputStream inputStream = classPathResource.getInputStream();

        File file = new File("java-tools/src/main/resources/template/年月日表头生成模板.xlsx");
        InputStream inputStream = new FileInputStream(file);

        // Workbook workbook = new HSSFWorkbook(inputStream); // xls
        Workbook workbook = new XSSFWorkbook(inputStream); // xlsx

        // 年份单元格
        int yearRow = 6;
        int yearColumn = 15;
        // 月份单元格
        int monthRow = 7;
        int monthColumn = 15;
        // 日期单元格
        int dateRow = 2;
        int dateColumn = 15;
        // 复制单元格模板列
        int dateColumnCopy = 15;

        for (Map.Entry<Integer, List<LocalDate>> yearEntry : map.entrySet()) {
            // 年份
            Integer year = yearEntry.getKey();
            // 当年日期
            List<LocalDate> yearDates = yearEntry.getValue();

            // 按月份分组
            Map<Integer, List<LocalDate>> monthMap = yearDates.stream().collect(Collectors.groupingBy(LocalDate::getMonthValue));
            for (Map.Entry<Integer, List<LocalDate>> entryEntry : monthMap.entrySet()) {
                // 月份
                Integer month = entryEntry.getKey();
                // 当月日期
                List<LocalDate> monthDates = entryEntry.getValue();
                for (LocalDate date : monthDates) {
                    if (dateColumn > dateColumnCopy) {
                        ExcelUtil.copyCell(workbook, 0, dateRow, dateColumnCopy, dateColumn, true);
                        ExcelUtil.copyCell(workbook, 0, monthRow, dateColumnCopy, dateColumn, true);
                        ExcelUtil.copyCell(workbook, 0, yearRow, dateColumnCopy, dateColumn, true);
                    }
                    try {
                        // yyyy-MM-dd转yyyy/MM/dd
                        String format = date.format(dateTimeFormatter2);
                        ExcelUtil.setCellValueDate(workbook, 0, dateColumn, dateRow, format);
                        dateColumn++;
                    } catch (Exception e) {
                        e.printStackTrace();
                    }
                }
                // 生成月份单元格 合并
                ExcelUtil.mergedRegion(workbook, 0, monthColumn, monthRow, monthColumn + monthDates.size() - 1, monthRow);
                ExcelUtil.setCellValueString(workbook, 0, monthColumn, monthRow, month);
                monthColumn = monthColumn + monthDates.size();
            }
            // 生成年份单元格 合并
            ExcelUtil.mergedRegion(workbook, 0, yearColumn, yearRow, yearColumn + yearDates.size() - 1, yearRow);
            ExcelUtil.setCellValueString(workbook, 0, yearColumn, yearRow, year);
            yearColumn = yearColumn + yearDates.size();
        }

        // 缩小年月日表头的列宽
        Sheet sheet = workbook.getSheetAt(0);
        for (int i = dateColumnCopy; i < dateColumn; i++) {
            sheet.setColumnWidth(i - 1, 30);
        }

        // 添加条件格式
        // https://blog.csdn.net/JustForFly/article/details/8060975
        // https://blog.csdn.net/kelly921011/article/details/125913626
        // 2个实现类：SheetConditionalFormatting SheetConditionalFormatting
        SheetConditionalFormatting scf = sheet.getSheetConditionalFormatting();

        // 规则 规则类型：使用公式确定要设置格式的单元格 =AND(O$2-$D7>=-5,O$2-$D7<5)
        ConditionalFormattingRule cf_R_green = scf.createConditionalFormattingRule("AND(O$2-$D7>=-5,O$2-$D7<5)");
        // 格式 单元格格式 图案 单元格底纹 颜色
        PatternFormatting cf_green = cf_R_green.createPatternFormatting();
        cf_green.setFillBackgroundColor(IndexedColors.GREEN.getIndex());

        // 规则 规则类型：使用公式确定要设置格式的单元格 =AND(O$2-$J7>=-5,O$2-$J7<5)
        ConditionalFormattingRule cf_R_blue = scf.createConditionalFormattingRule("AND(O$2-$J7>=-5,O$2-$J7<5)");
        // 格式 单元格格式 图案 单元格底纹 颜色
        PatternFormatting cf_blue = cf_R_blue.createPatternFormatting();
        // cf_blue.setFillBackgroundColor(IndexedColors.PALE_BLUE.getIndex());
        cf_blue.setFillBackgroundColor(IndexedColors.ROYAL_BLUE.getIndex());

        // 规则 规则类型：使用公式确定要设置格式的单元格 =AND(O$2-$I8>=0,O$2-$I8<25)
        ConditionalFormattingRule cf_R_red = scf.createConditionalFormattingRule("AND(O$2-$I8>=0,O$2-$I8<25)");
        // 格式 单元格格式 图案 单元格底纹 颜色
        PatternFormatting cf_red = cf_R_red.createPatternFormatting();
        cf_red.setFillBackgroundColor(IndexedColors.RED.getIndex());

        // 规则 规则类型：使用公式确定要设置格式的单元格 =($I7+1)=O$2
        ConditionalFormattingRule cf_R_black = scf.createConditionalFormattingRule("($I7+1)=O$2");
        // 格式 单元格格式 图案 单元格底纹 颜色
        PatternFormatting cf_black = cf_R_black.createPatternFormatting();
        cf_black.setFillBackgroundColor(IndexedColors.BLACK.getIndex());

        // 规则 规则类型：使用公式确定要设置格式的单元格 =AND(O$2>=$F7,O$2<=$G7)
        ConditionalFormattingRule cf_R_blue2 = scf.createConditionalFormattingRule("AND(O$2>=$F7,O$2<=$G7)");
        // 格式 单元格格式 图案 单元格底纹 颜色
        PatternFormatting cf_blue2 = cf_R_blue2.createPatternFormatting();
        cf_blue2.setFillBackgroundColor(IndexedColors.ROYAL_BLUE.getIndex());

        // 规则 规则类型：使用公式确定要设置格式的单元格 =AND(O$2>=$E7,O$2<=$I7)
        ConditionalFormattingRule cf_R_border = scf.createConditionalFormattingRule("AND(O$2>=$E7,O$2<=$I7)");
        // 格式 单元格格式 图案 单元格底纹 颜色
        BorderFormatting cf_border = cf_R_border.createBorderFormatting();
        cf_border.setBorderBottom(BorderStyle.MEDIUM);

        // ConditionalFormattingRule[] cfRules = {cf_R_green, cf_R_blue, cf_R_red, cf_R_black, cf_R_blue2, cf_R_border};
        ConditionalFormattingRule[] cfRules = {cf_R_green, cf_R_blue, cf_R_red};

        // 条件格式应用的单元格范围
        // 应用于 作用范围 =$O$8:$APR$9
        // dateColumn在最后1列完后，仍然被+1，所以需要再-1
        CellRangeAddress[] regions = {new CellRangeAddress(monthRow, monthRow + 1, dateColumnCopy - 1, dateColumn - 1 - 1)};

        // Number of rules must not exceed 3
        // cfRules 数组最大长度只能支持3个条件格式规则
        scf.addConditionalFormatting(regions, cfRules);

        ConditionalFormattingRule[] cfRules2 = {cf_R_black, cf_R_blue2, cf_R_border};
        scf.addConditionalFormatting(regions, cfRules2);

        ExcelUtil.saveExcelFile(workbook, "D:\\甘特图模板.xlsx");

        workbook.close();
        inputStream.close();

    }

    /**
     * 读取excel
     */
    private static void redaExcel() throws Exception {

        File file = new File("java-tools/src/main/resources/template/甘特图模板.xlsx");
        InputStream inputStream = new FileInputStream(file);

        // Workbook workbook = new HSSFWorkbook(inputStream); // xls
        Workbook workbook = new XSSFWorkbook(inputStream); // xlsx

        Sheet sheet = workbook.getSheetAt(0);
        // 获取模版的sheet中的条件样式对象
        SheetConditionalFormatting oldFormatting = sheet.getSheetConditionalFormatting();
        // 获取模版的sheet中有多少条件样式
        int num = oldFormatting.getNumConditionalFormattings();
        for (int i = 0; i < num; i++) {
            // 获取模版的sheet中的每一个条件样式
            ConditionalFormatting conditionalFormattingAt = oldFormatting.getConditionalFormattingAt(i);
            // 获取模版的sheet中的"条件样式"对应的单元格
            CellRangeAddress[] formattingRanges = conditionalFormattingAt.getFormattingRanges();

            // 获取模版的sheet中"条件"的下标
            int numberOfRules = conditionalFormattingAt.getNumberOfRules();
            for (int ruleIndex = 0; ruleIndex < numberOfRules; ruleIndex++) {
                // 通过下标获取模版的sheet中的"条件"
                ConditionalFormattingRule rule = conditionalFormattingAt.getRule(ruleIndex);
                System.out.println("rule1:" + rule.getFormula1());
                System.out.println("rule2:" + rule.getFormula2());
                if (rule.getPatternFormatting() != null) {
                    System.out.println("rule3:" + rule.getPatternFormatting().getFillBackgroundColor());
                    System.out.println("rule3:" + rule.getPatternFormatting().getFillBackgroundColorColor());
                    Color color = rule.getPatternFormatting().getFillBackgroundColorColor();
                    if (color != null && color instanceof XSSFColor) {
                        XSSFColor xssfColor = (XSSFColor) color;
                        System.out.println("rule3:" + xssfColor.getIndex());
                        System.out.println("rule3:" + xssfColor.getTheme());
                        System.out.println("rule3:" + xssfColor.getCTColor().getTheme());
                    }
                }
                if (rule.getBorderFormatting() != null && rule.getBorderFormatting().getBorderBottom() != null) {
                    System.out.println("rule4:" + rule.getBorderFormatting().getBorderBottom());
                }
            }
        }

        workbook.close();
        inputStream.close();

    }

    /**
     * Java 使用 java8 获取两个日期之间的所有日期
     * https://blog.csdn.net/llwlwl414/article/details/119991558
     *
     * @param startDate
     * @param endDate
     */
    public static List<LocalDate> getDatesBetween(LocalDate startDate, LocalDate endDate) {
        // Java 利用Temporal类和ChronoUnit类计算日期差 https://blog.csdn.net/weixin_44339331/article/details/92725516
        // 两天之间相差的天数
        long numOfDaysBetween = ChronoUnit.DAYS.between(startDate, endDate) + 1;
        // IntStream.iterate 通过迭代函数，生成一个有序无限的Int数据流 https://blog.csdn.net/qq_42145871/article/details/104939685
        // 为什么Java stream有了map还需要有mapToObj https://www.jianshu.com/p/7b5c0e3e22ce
        return IntStream.iterate(0, i -> i + 1).limit(numOfDaysBetween)
                .mapToObj(i -> startDate.plusDays(i)).collect(Collectors.toList());
    }

}
